/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.City;

/**
 *
 * @author Annette
 */
public class CityHandler {

    private DatabaseConnection db;

    public CityHandler(DatabaseConnection db) {
        this.db = db;
    }

    public ArrayList<City> getCities() throws SQLException {
        ArrayList<City> list = new ArrayList<>();

        String sql = "select id, c_name, country from city";
        ResultSet rs = db.selectQuery(sql);
        while (rs.next()) {
            list.add(new City(rs.getInt("id"), rs.getString("c_name"), rs.getString("country")));
        }
        return list;
    }

    public ArrayList<City> getValidCities(City city) throws SQLException {
        ArrayList<City> list = new ArrayList<>();

        String sql = "select id, c_name, country from city where id in (select destination from route where origin = " + city.getId() + ")";
        ResultSet rs = db.selectQuery(sql);
        while (rs.next()) {
            list.add(new City(rs.getInt("id"), rs.getString("c_name"), rs.getString("country")));
        }
        return list;
    }
}
